﻿using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;


namespace IFS_Gradebook_Generator.App_Code
{
    public class DataHelper
    {
        private const string accessDBconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
        //private const string access2007DBconn = "Provider=Microsoft.ACE.OLEDB.12.0;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=";

        public static List<Students> GetStudents(string grade)
        {
            string query;
            string querySELECT = "SELECT GradeDescription, FirstName, LastName, HomePhone FROM [Roll Call Sheet]";
            string queryWHERE = "WHERE GradeDescription = '" + grade + "'";
            string queryORDERBY = "ORDER BY GradeDescription, LastName, FirstName";

            if (string.IsNullOrEmpty(grade) || grade == "ALL" )
            {
                query = querySELECT + queryORDERBY;
            }
            else
            {
                query = querySELECT + queryWHERE + queryORDERBY;
            }

            var dAdapter = new OleDbDataAdapter(query, accessDBconn + Settings.Default.DatabasePath);

            var dTable = new DataTable();
            
            dAdapter.Fill(dTable);

            return LoadClassListFromDataTable(dTable);
        }

        public static List<Students> GetStudents2(string grade)
        {
            string query;
            string querySELECT = "SELECT GradeDescription, FirstName, LastName, HomePhone FROM [Roll Call Sheet]";
            string queryWHERE = "WHERE GradeDescription = '" + grade + "'";
            string queryORDERBY = "ORDER BY GradeDescription, LastName, FirstName";

            if (string.IsNullOrEmpty(grade) || grade == "ALL")
            {
                query = querySELECT + queryORDERBY;
            }
            else
            {
                query = querySELECT + queryWHERE + queryORDERBY;
            }

            var conn = new OleDbConnection(accessDBconn + Settings.Default.DatabasePath);

            var cmd = new OleDbCommand(query, conn);

            conn.Open();

            OleDbDataReader reader = cmd.ExecuteReader();
          
            List<Students> lstStudents = LoadClassListFromReader(reader);

            reader.Close();
            conn.Close();

            return lstStudents;
        }

        private static List<Students> LoadClassListFromReader(IDataReader reader)
        {
            var studentList = new List<Students>();
            while (reader.Read())
            {

                studentList.Add(new Students
                {
                    Grade = reader["GradeDescription"].ToString(),
                    FirstName = reader["FirstName"].ToString(),
                    LastName = reader["LastName"].ToString(),
                    PhoneNumber = reader["HomePhone"].ToString(),
                });
                              
            }

            return studentList;
        }

        public static List<Students> LoadClassListFromDataTable(DataTable table)
        {
            var studentList = new List<Students>();

            foreach (DataRow dr in table.Rows)
            {
                studentList.Add(new Students
                {
                    Grade = dr["GradeDescription"].ToString(),
                    FirstName = dr["FirstName"].ToString(),
                    LastName = dr["LastName"].ToString(),
                    PhoneNumber = dr["HomePhone"].ToString(),
                });
            }

            return studentList;
        }
    }
}
